package com.cs.paoyer.administration.business.entity;


//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.HashMap;
//import java.util.Vector;
//import java.sql.PreparedStatement;
//import java.util.Iterator;

import com.cs.paoyer.administration.business.model.FDVO;
import com.cs.paoyer.administration.business.model.ContractVO;
//import com.cs.paoyer.common.exception.UserException;
//import com.cs.paoyer.common.exception.SystemException;

/**
 * DAO para realizar las consultas de los fondos documentales.
 * 
 * @author Nicolas.Ceron@consult-soft.com
 * @version 1.0
 */

public class FDDAO 
{
  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;

  /**
   * Constructor que recibe como parametro la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public FDDAO(Connection conn){
    this.conn = conn;
  }

  
    public Collection findInventoryByCriterea(FDVO criterea) throws SQLException 
    {
      
				String query =  "SELECT * " +
                        "FROM vfd_inventario_fd infd " +
                        "WHERE infd.fecha_movimiento =  " +
                        "  (SELECT MAX(hifd.fecha_crea) "+
                        "  FROM tfd_historia_fd hifd "+  
                        "  WHERE hifd.fondo_documental = infd.nro_interno_fd"+ 
                        "  AND hifd.fecha_crea <= TO_DATE('"+criterea.getCutDate()+"','yyyy-mm-dd')) ";

				String filters = "";
				// Filtro por numero de fondo documental
				if ( (criterea.getFdType() != null) && !(criterea.getFdType().longValue()==0) ) 
				{
						if(criterea.getFdType().longValue()==1)
						{
								filters = "AND infd.nro_interno_fd='" + criterea.getFd() + "' " ;
						}
						else
						{
								filters = "AND infd.nro_cliente_fd='" + criterea.getFd().toUpperCase().trim() + "' " ;
						}

						
				}
				// Filtro por sucursal
				if ( (criterea.getBranchId() != null) && !(criterea.getBranchId().longValue()==0) ) 
				{
						filters = filters + " AND infd.codin_sucursal=" + criterea.getBranchId();
				}  
				// Filtro por Contrato
				if ( (criterea.getContract() != null) && !(criterea.getContract().equals("")) ) 
				{
					filters = filters + " AND UPPER(infd.nom_contrato)='" + criterea.getContract().toUpperCase() + "'";
				}
				// Filtro por Bodega
				if ( (criterea.getWarehouseId() != null) && !(criterea.getWarehouseId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_bodega =" + criterea.getWarehouseId();
				}
        // Filtro por Centro de costo
				if ( (criterea.getCostCenterId() != null) && !(criterea.getCostCenterId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_centro_costo =" + criterea.getCostCenterId();
				}
				// Filtro por Centro de costo
				if ( (criterea.getFdClassId() != null) && !(criterea.getFdClassId().longValue()==0) ) 
				{
						filters = filters + " AND infd.codin_clase_fd =" + criterea.getFdClassId();
				}

				// Filtro por tipo
				if ( (criterea.getFdTypeId() != null) && !(criterea.getFdTypeId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_tipo_fd =" + criterea.getFdTypeId();
				}
				
				
				// Filtro por Codigo Interno de Autorizado
				if ( (criterea.getAuthorizedCode() != null) && !(criterea.getAuthorizedCode().longValue()==0) ) 
				{
					filters = filters + " AND infd.codin_autorizado = " + criterea.getAuthorizedCode();
				}

				// Si hay filtros, entonces armar la clausula WHERE 
				if ( !filters.equalsIgnoreCase("") ) {
					query = query + filters;
				}


				Statement stm = null;
				ResultSet rst = null;
				ArrayList fds = new ArrayList();
				stm = conn.createStatement();
				rst = stm.executeQuery(query);
				FDVO fd = null;
				while ( rst.next() ) 
				{
					  fd = new FDVO();
						fd.setContract(rst.getString("nom_contrato"));
						fd.setFdClassName(rst.getString("nom_clase_fd"));
						fd.setFdTypeName(rst.getString("nom_tipo_fd"));
						fd.setFd(rst.getString("nro_interno_fd"));
						fd.setLocation(rst.getString("ubicacion_fd"));
						fd.setClientICode(rst.getString("nro_cliente_fd"));
						fd.setSize(rst.getString("tamano"));
						fd.setEntranceDate(rst.getDate("fecha_ingreso_fd").toString());
						fds.add(fd);
						fd = null;
			 }
         
			 stm.close();
			 return fds;
  }



    public Collection findFDHistoryByCriterea(FDVO criterea) throws SQLException 
    {
      
				String query =  "SELECT * " +
                        "FROM vfd_inventario_fd infd " +
                        "WHERE infd.fecha_movimiento =  ";

				String filters = "";
				// Filtro por numero de fondo documental
				if ( (criterea.getFdType() != null) && !(criterea.getFdType().longValue()==0) ) 
				{
						if(criterea.getFdType().longValue()==1)
						{
								filters = "AND infd.nro_interno_fd='" + criterea.getFd() + "' " ;
						}
						else
						{
								filters = "AND infd.nro_cliente_fd='" + criterea.getFd().toUpperCase().trim() + "' " ;
						}

						
				}
				// Filtro por sucursal
				if ( (criterea.getBranchId() != null) && !(criterea.getBranchId().longValue()==0) ) 
				{
						filters = filters + " AND infd.codin_sucursal=" + criterea.getBranchId();
				}  
				// Filtro por Contrato
				if ( (criterea.getContract() != null) && !(criterea.getContract().equals("")) ) 
				{
					filters = filters + " AND UPPER(infd.nom_contrato)='" + criterea.getContract().toUpperCase() + "'";
				}
				// Filtro por Bodega
				if ( (criterea.getWarehouseId() != null) && !(criterea.getWarehouseId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_bodega =" + criterea.getWarehouseId();
				}
        // Filtro por Centro de costo
				if ( (criterea.getCostCenterId() != null) && !(criterea.getCostCenterId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_centro_costo =" + criterea.getCostCenterId();
				}
				// Filtro por Centro de costo
				if ( (criterea.getFdClassId() != null) && !(criterea.getFdClassId().longValue()==0) ) 
				{
						filters = filters + " AND infd.codin_clase_fd =" + criterea.getFdClassId();
				}

				// Filtro por tipo
				if ( (criterea.getFdTypeId() != null) && !(criterea.getFdTypeId().longValue()==0) ) 
				{
						filters = filters + " AND infd.cod_tipo_fd =" + criterea.getFdTypeId();
				}
				// Filtro por Fecha de corte
				if ( (criterea.getCutDate() != null) && !(criterea.getCutDate().equals("")) ) 
				{
					filters = filters + " AND infd.fecha_movimiento = TO_DATE('" + criterea.getCutDate().toUpperCase() + "','yyyy-mm-dd')";
				}
				
				// Filtro por Codigo Interno de Autorizado
				if ( (criterea.getAuthorizedCode() != null) && !(criterea.getAuthorizedCode().longValue()==0) ) 
				{
					filters = filters + " AND infd.codin_autorizado = " + criterea.getAuthorizedCode();
				}

				// Si hay filtros, entonces armar la clausula WHERE 
				if ( !filters.equalsIgnoreCase("") ) {
					query = query + filters;
				}


				Statement stm = null;
				ResultSet rst = null;
				ArrayList fds = new ArrayList();
				stm = conn.createStatement();
				rst = stm.executeQuery(query);
				FDVO fd = null;
				while ( rst.next() ) 
				{
					  fd = new FDVO();
						fd.setContract(rst.getString("nom_contrato"));
						fd.setFdClassName(rst.getString("nom_clase_fd"));
						fd.setFdTypeName(rst.getString("nom_tipo_fd"));
						fd.setFd(rst.getString("nro_interno_fd"));
						fd.setLocation(rst.getString("ubicacion_fd"));
						fd.setClientICode(rst.getString("nro_cliente_fd"));
						fd.setSize(rst.getString("tamano"));
						fd.setEntranceDate(rst.getDate("fecha_ingreso_fd").toString());
						fds.add(fd);
						fd = null;
			 }
         
			 stm.close();
			 return fds;
  }

  public Collection findContractxAuthorizedByCriterea(ContractVO criterea) throws SQLException 
    {
    
      
				String query = "SELECT cxa.* FROM vfd_contratos_x_autorizado cxa";

				String filters = "";
				// Filtro por codigo del autorizado
				if ( (criterea.getUserId() != null) && !(criterea.getUserId().longValue()==0) ) 
				{
						if(filters.equalsIgnoreCase(""))
						{
								filters = " autr_codusu='" + criterea.getUserId() + "' " ;
						}
						else
						{
								filters = "and autr_codusu='" + criterea.getUserId() + "' " ;
						}			
				}
				
				// Si hay filtros, entonces armar la clausula WHERE 
				if ( !filters.equalsIgnoreCase("") ) 
        {
					query = query + " where" + filters;
				}

				Statement stm = null;
				ResultSet rst = null;
				ArrayList contracts = new ArrayList();
				stm = conn.createStatement();
				rst = stm.executeQuery(query);
				ContractVO contract = null;
				while ( rst.next() ) 
				{
					  contract = new ContractVO();
						contract.setParallelCode(rst.getString("cont_codusu"));
            contract.setContractInternalCode(new Long(rst.getLong("cont_codin")));
						contracts.add(contract);
						contract = null;
			 }
         
			 stm.close();
			 return contracts;
  }

		  
}